package api.utils;

import api.ApiTest;
import lombok.SneakyThrows;
import org.apache.commons.collections4.MapUtils;
import org.apache.poi.ss.usermodel.*;

import java.io.FileInputStream;
import java.io.InputStream;
import java.util.*;

//https://www.jianshu.com/p/2ba3c0bd3eb6
public class ExcelToMapUtils {

    public static List<String> getAllBuniess(String path)  {
        Workbook workbook = null;
        List<String> list =new ArrayList<>();
        try {
            workbook = WorkbookFactory.create(new FileInputStream(path));
            for (int index = 1; index < workbook.getNumberOfSheets(); index++){
                Sheet sheet = workbook.getSheetAt(index);
                list.add(sheet.getSheetName());
            }
        }catch (Exception ex){
            ex.printStackTrace();
        }finally {
            try{
                if(workbook!=null){
                    workbook.close();
                }

            }catch (Exception ex){
                ex.printStackTrace();
            }
        }
        return list;
    }
    public static List<Map<String,Object>> importExcel(String path,int index)  {
        List<Map<String,Object>> mapList = new ArrayList<Map<String,Object>>();
        Workbook workbook = null;
        try{
            workbook = WorkbookFactory.create(new FileInputStream(path));
            Sheet sheet = workbook.getSheetAt(index);
            Row row = sheet.getRow(0);
            List<String> keys = new ArrayList<String>();
            if(row==null){
                return mapList;
            }
            for(int i = 0; i < row.getLastCellNum(); i++){
                Cell cell = row.getCell(i);
                keys.add(String.valueOf(getValue(cell)));
            }

            for(int i = 1; i <= sheet.getLastRowNum(); i++){
                Row currentRow = sheet.getRow(i);
                if(currentRow!=null) {
                    Map<String, Object> map = new LinkedHashMap<>();
                    for (int j = 0; j < currentRow.getLastCellNum(); j++) {
                        map.put(keys.get(j), getValue(currentRow.getCell(j)));
                    }
                    if(MapUtils.isNotEmpty(map)){
                        mapList.add(map);
                    }
                }
            }
        }catch (Exception ex){
            //System.out.println("-----");
            ex.printStackTrace();
        }finally {
            try{
                if(workbook!=null){
                    workbook.close();
                }

            }catch (Exception ex){
                ex.printStackTrace();
            }

        }
        return mapList;
    }

    private static Object getValue(Cell cell) {
        if (cell.getCellType() == CellType.BOOLEAN) {
            return cell.getBooleanCellValue();
        } else if (cell.getCellType() == CellType.NUMERIC) {
            return cell.getNumericCellValue();
        } else {
            return String.valueOf(cell.getStringCellValue()).trim();
        }
    }

    @SneakyThrows
    public static void main(String[] args) {
       String filePath = ApiTest.dir +"apitest3.xlsx";
//        List<Map<String, Object>> maps = importExcel(filePath, 1);
//        System.out.println(maps);

        List<String> allBuniess = getAllBuniess(filePath);
        System.out.println(allBuniess);
    }
}
